package sdfi.lee.Newpj.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import sdfi.lee.DAO.DAO;
import sdfi.lee.Newpj.Entity.Achievement;
import sdfi.lee.Newpj.Entity.I_Achievement;
import sdfi.lee.Newpj.Entity.Product;
import sdfi.lee.Properties.ReadXMLFromString;

public class AchievementDAO {
	public List<Achievement> getAchivement_qu(int pg,int size){
		List<Achievement> list=new ArrayList<Achievement>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			con.setAutoCommit(false);
			String sql="select id, title,dtype,content,ddate from(";
			sql+="select id as id,drugname as title,0 as dtype,'hehhe' as content,drugdate as ddate from drug ";
			sql+=" union ";
			sql+="select id as id,rolltitle as title,1 as dtype,rollpath as content,rolldate as ddate from rollnotice " +
					"where rolltype>=6 and rolltype <=8 ";
			sql+=") as u order by ddate desc limit "+size*(pg-1)+","+size;
			
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				Achievement  ach=new Achievement();
				ach.setDt(rs.getDate("ddate"));
				ach.setId(rs.getInt("id"));
				ach.setPath(rs.getString("content"));
				ach.setName(rs.getString("title"));
				ach.setType(rs.getInt("dtype"));
				list.add(ach);
			}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			list=null;
		}
		finally{
			DAO.closeSession();
		}
		return list;
	}

	public List<Achievement> getAchivement_qu(String id,int size,int type){
		List<Achievement> list=new ArrayList<Achievement>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			con.setAutoCommit(false);
			String sql="select id, title,dtype,content,ddate from(";
			if(type==0)
			{
			sql+="select id as id,drugname as title,0 as dtype,'hehhe' as content,drugdate as ddate from drug ";
			sql+="where id>"+id+"";
			}else
			{
			sql+="select id as id,rolltitle as title,1 as dtype,rollpath as content,rolldate as ddate from rollnotice " +
					"where rolltype>=6 and rolltype <=8 and id>"+id+"";
			}
			sql+=") as u order by id asc limit 0,5";
			System.out.println(sql);
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				Achievement  ach=new Achievement();
				ach.setDt(rs.getDate("ddate"));
				ach.setId(rs.getInt("id"));
				ach.setPath(rs.getString("content"));
				ach.setName(rs.getString("title"));
				ach.setType(rs.getInt("dtype"));
				list.add(ach);
			}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			list=null;
		}
		finally{
			DAO.closeSession();
		}
		return list;
	}

	public List<I_Achievement> getI_Achievement(int pg,int size,String author,String name,String manufacturer,String content)
	{
		List<I_Achievement> list=new ArrayList<I_Achievement>();
		if("".equals(name))name=null;
		if("".equals(manufacturer)) manufacturer=null;
		if("".equals(content)) content=null;
		String sql="select answer from answer where type=0 ";
		if(author!=null)
			sql+="and answer like '%"+author.trim() +"%' ";
		if(name!=null)
		
			sql+="and answer like '%"+name.trim()+"%' ";
			
		if(manufacturer!=null)
		
			sql+="and answer like '%"+manufacturer.trim()+"%' ";
		
		if(content!=null)
			sql+="and answer like '%"+content.trim()+"'content%' ";
		if(pg>0)
			sql+="limit "+(pg-1)*size+","+size;
		Session session=DAO.openSession();
		Connection con=session.connection();
		
		try
		{
		con.setAutoCommit(false);
		ResultSet rs=con.createStatement().executeQuery(sql);
		con.commit();
		while(rs.next())
		{
			ReadXMLFromString rxfs=new ReadXMLFromString();
			rxfs.setSource(rs.getString("answer"));
			I_Achievement a=new I_Achievement();
			a.setAuthor(rxfs.readerValueByNode("/profile/CE[@name='第一完成人']/NE/@name"));
			a.setContent(rxfs.readerValueByNode("/profile/CE[@name='成果简介']/NE/@name"));
			a.setName(rxfs.readerValueByNode("/profile/@NE"));
			a.setManufacturer(rxfs.readerValueByNode("/profile/CE[@name='主要完成单位']/NE/@name"));
			list.add(a);
		}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}
	
	public int getAchievementAccount()
	{
	
		try
		{
			
			String sql="select count(*) as c from(";
			sql+="select id as id,drugname as title,0 as dtype,'hehhe' as content,drugdate as ddate from drug ";
			sql+=" union ";
			sql+="select id as id,rolltitle as title,1 as dtype,rollpath as content,rolldate as ddate from rollnotice " +
					"where rolltype>=6 and rolltype <=8 ";
			sql+=") as u order by ddate desc";
			
			return DAO.getAccount(sql);
			
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			return 0;
		}
		
	}
	public List<Product> getProduct(int pg,int size,String name,String medicament,String manufacturer,String appnumb)
	{
		List<Product> list=new ArrayList<Product>();
		if("".equals(name)) name=null;
		if("".equals(medicament)) medicament=null;
		if("".equals(manufacturer)) manufacturer=null;
		if("".equals(appnumb)) appnumb=null;;
		String sql="select id,answer from answer where type=-1 " ;
		if(name!=null) sql+="and answer like '%"+name+"%' ";
		if(medicament!=null) sql+="and answer like '%"+medicament+"%' ";
		if(manufacturer!=null) sql+="and answer like '%"+manufacturer+"%' ";
		if(appnumb!=null) sql+="and answer like '%"+appnumb+"%' ";
		sql+="limit "+(pg-1)*size+","+size;
		Session session=DAO.openSession();
		
		Connection con=session.connection();
		try
		{
			con.setAutoCommit(false);	
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				ReadXMLFromString rxfs=new ReadXMLFromString();
				rxfs.setSource(rs.getString("answer"));
				Product p=new Product();
				p.setName(rxfs.readerValueByNode("/profile/@NE"));
				//System.out.println(p.getName()+rs.getInt("id"));
				p.setManufacturer(rxfs.readerValueByNode("/profile/CE[@name='CeManufacturer']/NE/@name"));
				p.setAppnumb(rxfs.readerValueByNode("/profile/CE[@name='CeApprovalNumber']/NE/@name"));
				p.setMedicament(rxfs.readerValueByNode("/profile/CE[@name='CeMedicament']/NE/@name"));
				list.add(p);
			}
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

}
